<template>
  <div class="card-page">
    <tool-bar nav="" :title='"数据库差异"'>
      <el-button size="small" @click="$router.push('/dev/db-connection/add')" v-if="$props.action=='edit'">新建
      </el-button>
    </tool-bar>
    <el-row :gutter="12" type="flex">
      <el-col :span="12">
        <el-card shadow="always" style="position: relative;overflow: visible">
          <template #header><span>参照数据库</span>

            <el-button :icon="Switch" type="primary" style="position: absolute;top:-34px;right:-34px"
                       @click="switch_db_click">
              切换
            </el-button>
          </template>

          <tables-com :id="$data.id1" @loaded="db1_loaded" @change="db1_change" readonly :tables="$data.tables1"
                      :info="$data.info1"></tables-com>

        </el-card>
      </el-col>
      <el-col :span="12">
        <el-card shadow="always">

          <template #header><span>对比数据库</span></template>

          <tables-com :id="$data.id2" @loaded="db2_loaded" @change="db2_change" readonly :tables="$data.tables2"
                      :info="$data.info2"></tables-com>
        </el-card>
      </el-col>
    </el-row>

    <el-card shadow="always">
      <template #header><span>增加表 {{ $data.addedTables.length }}</span>
      </template>
      <el-tag v-for="item in $data.addedTables">{{ item.name }}</el-tag>

      <pre>{{ $data.addedTables_sql }}</pre>
    </el-card>

    <el-card shadow="always">
      <template #header><span>删除表 {{ $data.deletedTables.length }}</span>
      </template>
      <el-tag v-for="item in $data.deletedTables">{{ item.name }}</el-tag>

      <pre>{{ $data.deletedTables_sql }}</pre>
    </el-card>

    <el-card shadow="always">
      <template #header><span>修改表 {{ $data.changedTables.length }}</span>
      </template>
      <div v-for="item in $data.changedTables" style="display: flex;flex-direction: column;align-items: flex-start;">
        <el-tag>{{ item.tableName }}</el-tag>

        <div v-if="item.addedColumns.length" style="margin-left:6px;">
          增加列: {{ item.addedColumns.map(it => it.name).join("; ") }}
        </div>
        <div v-if="item.deletedColumns.length" style="margin-left:6px;">
          删除列: {{ item.deletedColumns.map(it => it.name).join("; ") }}
        </div>
        <div v-if="item.changedColumns.length" style="margin-left:6px;">
          修改列: {{ item.changedColumns.map(it => get_changed_column(item.tableName, it.name)).join("; ") }}
        </div>
        <!--        <div v-if="item.remark_changed.length" style="margin-left:6px;">-->
        <!--          备注: {{ item.remark_changed.join("; ") }}-->
        <!--        </div>-->
      </div>


      <pre>{{ $data.changedTables_sql }}</pre>
    </el-card>

    <el-card shadow="always">
      <template #header><span>表索引 {{ $data.indexes.length }}</span>
      </template>
      <div v-for="item in $data.indexes" style="display: flex;flex-direction: column;align-items: flex-start;">
        <el-tag>{{ item.tableName }}</el-tag>

        <div v-if="item.addedIndexes.length" style="margin-left:6px;">
          增加索引: {{ item.addedIndexes.map(it => it.type + ":" + it.columns).join("; ") }}
        </div>
        <div v-if="item.deletedIndexes.length" style="margin-left:6px;">
          删除索引: {{ item.deletedIndexes.map(it => it.type + ":" + it.columns).join("; ") }}
        </div>
      </div>


      <pre>{{ $data.indexes.map(it => it.sqls).unwind().sort().join("\n") }}</pre>
    </el-card>

  </div>
</template>
<style scoped lang="scss">
pre {
  background-color: linen;
  padding: 10px;
  margin: 5px auto;
  zoom: 0.9;
}
</style>
<script setup>
/**
 * Created by CodeGenerator  at 2021-04-11 23:42:19
 */

import tablesCom from "./tables-com.vue"
import {Switch} from '@element-plus/icons-vue'

//定义 vue2的this,当前组件
const $self = getCurrentInstance();

//定义 $attrs
const $attrs = useAttrs();


//定义data
const $data = reactive({
  DatabaseEnum: jv.enum.SqlDatabaseEnum.getData(),
  info1: {}, //子对象需要声明。
  tables1: [], //子对象需要声明。
  info2: {},
  tables2: [],
  indexes1: [],
  indexes2: [],
  indexes1_loaded: false,
  indexes2_loaded: false,
  select_tables1: [],
  select_tables2: [],
  id1: "",
  id2: "",
  addedTables: [],
  deletedTables: [],
  changedTables: [],
  indexes: []
});
const info = $computed(() => $data.info);

$data.id1 = jv.$route.query.table1;
$data.id2 = jv.$route.query.table2;


//定义事件
const $emit = defineEmits(["update:modelValue", "loaded"]);

const get_changed_column = function (tableName, columnName) {

  var table1 = $data.tables1.find(it => it.name == tableName);
  var table2 = $data.tables2.find(it => it.name == tableName);

  var column1Def = table1.columns.find(it => it.name == columnName);
  var column2Def = table2.columns.find(it => it.name == columnName);

  //a.sqlType != b.sqlType || a.charLength != b.charLength || a.columnDefault != b.columnDefault
  var txt = columnName + ":";
  if (column1Def.sqlType != column2Def.sqlType) {
    txt += `${column2Def.sqlType}↦${column1Def.sqlType}`
  } else if (column1Def.charLength != column2Def.charLength) {
    txt += `${column1Def.sqlType}(${column2Def.charLength}↦${column1Def.charLength})`
  }

  if (column1Def.columnDefault != column2Def.columnDefault) {
    if (!txt.endsWith(":")) {
      txt += ","
    }
    txt += `default(${column2Def.columnDefault}↦${column1Def.columnDefault})`
  }

  return txt;
}

const db1_loaded = async (result) => {
  $data.info1 = result.info || $data.info1;
  $data.tables1 = result.tables || $data.tables1;
  $data.indexes1 = result.indexes || $data.indexes1;
  $data.indexes1_loaded = $data.indexes1_loaded || result.type == "index";

  show_diff();
}

const db1_change = function (result) {
  $data.select_tables1 = result;
}


const db2_loaded = async (result) => {
  $data.info2 = result.info || $data.info2;
  $data.tables2 = result.tables || $data.tables2;
  $data.indexes2 = result.indexes || $data.indexes2;
  $data.indexes2_loaded = $data.indexes2_loaded || result.type == "index";

  show_diff();
}

const db2_change = function (result) {
  $data.select_tables2 = result;
}

const swap = function (data, key1, key2) {
  var tmp = data[key1];
  data[key1] = data[key2];
  data[key2] = tmp;
}

const switch_db_click = function () {
  swap($data, "id1", "id2")
  swap($data, "tables1", "tables2")
  swap($data, "info1", "info2")
  swap($data, "indexes1", "indexes2")

  show_diff()
}

const gen_column_def = function (column) {
  var nulable = column.isNullable ? "null" : "not null"

  nulable += " ";

  /**
   * 可能的值
   * columnDefault == null && isNullable == true   --> 默认是null 生成 default null
   * columnDefault == null && isNullable == false  --> 无默认值  --> 不生成 default
   * columnDefault == 1 && sqlType == number --> 数字1 --> 生成 default (1)
   * columnDefault == 1 && sqlType == varchar --> 字符串1  --> 生成 default '1'
   * columnDefault == "" && sqlType == number --> 默认数字  --> 生成 default (0)
   * columnDefault == "" && sqlType == varchar --> 空字符串  --> 生成 default ''
   */
  if (jv.isNull(column.columnDefault)) {
    if (column.isNullable) {
      nulable += "default null"
    }
  } else {
    var defSql = column.columnDefault;

    if (column.sqlType == "varchar" ||
        column.sqlType == 'char' ||
        column.sqlType == "text") {
      if (defSql) {
        nulable += "default '" + defSql + "'"
      } else {
        nulable += "default '' "
      }
    } else if (defSql.includes("(") && defSql.includes(")")) {
      nulable += "default (" + defSql + ")"
    } else if (defSql.startsWith("b'") && defSql.endsWith("'")) {
      nulable += "default " + defSql.slice(2, -1);
    } else if (defSql.startsWith("_utf8mb4\\'") && defSql.endsWith("\\'")) {
      nulable += "default '" + defSql.slice("_utf8mb4\\'".length, -2) + "'";
    } else {
      nulable += "default " + defSql
    }
  }

  if (column.name == "update_at") {
    nulable += " on update CURRENT_TIMESTAMP "
  }
  return `${column.columnDefineSql}  ${nulable}  comment '${column.comment}'`;
}

const gen_column_ddl = function (columnDef) {
  return `\`${columnDef.name}\` ${gen_column_def(columnDef)}`;
}


const gen_create_table_ddl = function (table) {
  var pks = table.columns.filter(it => it.isPrimary).map(it => "`" + it.name + "`").join(", ");
  var pkDef = "";
  if (pks) {
    pkDef = `,
    primary key ( ${pks} )`
  }
// @formatter:off
  return `create table if not exists ${table.name}
(
${table.columns.map(it => "    " + gen_column_ddl(it)).join(",\n")}${pkDef}
)
comment '${table.comment}';
  `
  // @formatter:on
}

/**
 * 生成改变的SQL
 * @param table
 * @returns {string}
 */
const gen_change_ddl = function (table) {
  var tableName = table.tableName;
  var list = [];
  list.addAll(table.addedColumns.map(it => `call AddColumnUnlessExists(database(),  '${tableName}',  '${it.name}', '${gen_column_def(it).replaceAll("'", "''")} ' );`));
  list.addAll(table.deletedColumns.map(it => `call DropColumnIfExists(database(),  '${tableName}',  '${it.name}' );`));

  table.changedColumns.forEach(changed => {
    var columnName = changed.name;
    var diff = changed.diff;
    var def = changed.def;
    list.push(`/*${diff}*/ call ChangeColumnIfExists(database(), '${tableName}', '${columnName}', '${gen_column_def(def).replaceAll("'", "''")} ' );`);
  })

  return list;
}

const saveTypes = "timestamp,datetime".split(";")
    .map(it => {
      var sect = it.split(",");
      return {v1: sect[0], v2: sect[1]};
    });

const sameValues = "_utf8mb4\\'\\',;CURRENT_TIMESTAMP,now()"
    .split(";")
    .map(it => {
      var sect = it.split(",");
      return {v1: sect[0], v2: sect[1]};
    });

const isSameType = function (a, b) {
  return saveTypes.find(it => (it.v1 == a && it.v2 == b) || (it.v1 == b && it.v2 == a)) != null;
}

const isSameValue = function (a, b) {
  if (!a && !b) {
    return true;
  }

  if (a.isNumberFormat() && b.isNumberFormat() && parseFloat(a) == parseFloat(b)) {
    return true;
  }
  return sameValues.find(it => (it.v1 == a && it.v2 == b) || (it.v1 == b && it.v2 == a)) != null;
}

const show_diff = function () {
  if (!$data.tables1.length || !$data.tables2.length || !$data.indexes1_loaded || !$data.indexes2_loaded) {
    return;
  }

  show_table_diff();
  show_index_diff();
}


const show_table_diff = function () {
  if (!$data.tables1.length || !$data.tables2.length) {
    return;
  }

  $data.addedTables = [];
  $data.deletedTables = [];
  $data.changedTables = [];


  $data.addedTables = $data.tables1.minus($data.tables2, (a, b) => a.name == b.name)
      .sort((a, b) => {
        return String.keySorter(a.name, b.name)
      });

  $data.addedTables_sql = $data.addedTables.map(it => gen_create_table_ddl(it)).join("\n");

  $data.deletedTables = $data.tables2.minus($data.tables1, (a, b) => a.name == b.name)
      .sort((a, b) => {
        return 0 - String.keySorter(a.name, b.name)
      });

  $data.deletedTables_sql = $data.deletedTables
      .map(it => `drop table if exists ${it.name};`).join("\n");

  var changed = [];
  var commonTables = $data.tables1.intersect($data.tables2, (a, b) => a.name == b.name).map(it => it.name);
  commonTables.forEach(tableName => {
    var table1 = $data.tables1.find(it => it.name == tableName);
    var table2 = $data.tables2.find(it => it.name == tableName);
    var commonColumns = table1.columns.intersect(table2.columns, (a, b) => a.name == b.name).map(it => it.name);


    //key==column, value=diffType
    var changedColumns = [];
    commonColumns
        .forEach(column => {
              var a = table1.columns.find(it => it.name == column);
              var b = table2.columns.find(it => it.name == column);

              if (a.sqlType != b.sqlType && !isSameType(a.sqlType, b.sqlType)) {
                return changedColumns.push({name: column, diff: "type", def: a});
              }

              if (a.charLength != b.charLength) {
                return changedColumns.push({name: column, diff: "length", def: a})
              }

              //CURRENT_TIMESTAMP == now()
              if (a.columnDefault != b.columnDefault && !isSameValue(a.columnDefault, b.columnDefault)) {
                return changedColumns.push({name: column, diff: "defaultValue", def: a})
              }

              if (a.comment.replaceAll(" ", "") != b.comment.replaceAll(" ", "")) {
                return changedColumns.push({name: column, diff: "comment", def: a});
              }
            }
        );

    // var changedColumns = table1.columns.filter(it => it.name.IsIn(changedColumnNames));


    var added = table1.columns.minus(commonColumns, (a, b) => a.name == b);
    var deleted = table2.columns.minus(commonColumns, (a, b) => a.name == b);


    // var remarkChangedColumns = commonColumns
    //     .map(column => {
    //           var a = table1.columns.find(it => it.name == column);
    //           var b = table2.columns.find(it => it.name == column);
    //
    //           if (a.comment.replaceAll(" ", "") != b.comment.replaceAll(" ", "")) {
    //             return a.comment + "->" + b.comment;
    //           }
    //           return "";
    //         }
    //     ).filter(it => it);


    if (added.length || deleted.length || changedColumns.length) {
      changed.push({tableName, addedColumns: added, deletedColumns: deleted, changedColumns: changedColumns});
    }
  })

  $data.changedTables = changed;
  $data.changedTables_sql = $data.changedTables.map(it => gen_change_ddl(it)).unwind().sort().join("\n");
}

const inNewTable = function (tableName) {
  return $data.addedTables.find(it => it.name == tableName) != null;
}

const inDropTable = function (tableName) {
  return $data.deletedTables.find(it => it.name == tableName) != null;
}

const show_index_diff = function () {
  if (!$data.indexes1_loaded || !$data.indexes2_loaded) {
    return;
  }

  $data.indexes = [];
  var tables1 = $data.indexes1.map(it => it.tableName);
  var tables2 = $data.indexes2.map(it => it.tableName);
  var allTables = tables1.union(tables2);

  var indexEq = (a, b) => a.type == b.type && a.columns == b.columns
  allTables.forEach(tableName => {
    var i1 = $data.indexes1.filter(it => it.tableName == tableName);
    var i2 = $data.indexes2.filter(it => it.tableName == tableName);


    var i1_indexes = [];
    i1.groupBy(it => it.indexName).forEach(it => {
      var indexName = it.data[0].indexName;
      var type = it.data[0].type;
      var columns = it.data.map(it => it.columnName).join(",");
      i1_indexes.push({tableName, indexName, type, columns});
    })


    var i2_indexes = [];
    i2.groupBy(it => it.indexName).forEach(it => {
      var indexName = it.data[0].indexName;
      var type = it.data[0].type;
      var columns = it.data.map(it => it.columnName).join(",");
      i2_indexes.push({tableName, indexName, type, columns});
    })


    var added = [];
    var deleted = [];
    var indexes_sql = [];

    i1_indexes.minus(i2_indexes, indexEq).forEach(it => {
      added.push(it);
      var type = it.type;

      if (inNewTable(tableName) && type == "PRIMARY") {
        indexes_sql.push(`-- 创建表 ${tableName} 已添加主键`);
        return;
      }

      var tables2Exists = $data.tables2.some(it => it.name == tableName);

      if (type == "PRIMARY") {
        if (tables2Exists) {
          indexes_sql.push(`/*+primary*/ alter table ${tableName} add primary key (${it.columns});`);
        } else {
          indexes_sql.push(`/*+primary alter table ${tableName} add primary key (${it.columns});*/`);
        }
      } else {
        if (type == "NORMAL") {
          type = "";
        }


        if (tables2Exists) {
          var uniquePrefix = type == 'UNIQUE' ? '/*+unique*/' : ''
          indexes_sql.push(`${uniquePrefix} call AddIndexUnlessExists(database(), '${tableName}', '${it.indexName}', '${it.columns}', '${type}' );`);
        } else {
          var uniquePrefix = type == 'UNIQUE' ? '/*+unique' : '/*'
          indexes_sql.push(`${uniquePrefix}call AddIndexUnlessExists(database(), '${tableName}', '${it.indexName}', '${it.columns}', '${type}' );*/`);
        }
      }
    });

    i2_indexes.minus(i1_indexes, indexEq).forEach(it => {
      deleted.push(it);
      if (inDropTable(tableName)) {
        if (it.type == "PRIMARY") {
          indexes_sql.push(`-- 删除表 ${tableName} 自动移除主键`);
        } else {
          indexes_sql.push(`-- 删除表 ${tableName} 自动移除索引 ${it.indexName}`);
        }
        return;
      }

      if (it.type == "PRIMARY") {
        indexes_sql.push(`/*-primary*/ alter table ${tableName} drop primary key;`);
      } else {
        indexes_sql.push(`drop index if exists ${it.indexName} on table_name ${tableName};`);
      }
    });

    if (added.length || deleted.length) {
      $data.indexes.push({tableName, addedIndexes: added, deletedIndexes: deleted, sqls: indexes_sql})
    }

  });

}
</script>
